#region License, Terms and Conditions
//
// Prawks - Copyright (c) 2007 Atif Aziz. All rights reserved.
//
// This library is free software; you can redistribute it and/or modify it under
// the terms of the GNU Lesser General Public License as published by the Free
// Software Foundation; either version 2.1 of the License, or (at your option)
// any later version.
//
// This library is distributed in the hope that it will be useful, but WITHOUT
// ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
// FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
// details.
//
// You should have received a copy of the GNU Lesser General Public License
// along with this library; if not, write to the Free Software Foundation, Inc.,
// 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 
//
#endregion

namespace Prawks.SqlMethods.CodeGeneration
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlTypes;
    using System.Data.SqlClient;
    using System.CodeDom;

    using ArrayList = System.Collections.ArrayList;
    using Debug = System.Diagnostics.Debug;
    using Regex = System.Text.RegularExpressions.Regex;

    public delegate string SymbolNamingHandler(string name);

    [ Serializable ]
    public sealed class FeedbackEventArgs : EventArgs
    {
        readonly string _text;

        internal FeedbackEventArgs(string text)
        {
            Debug.Assert(text != null);

            _text = text;
        }

        public string Text
        {
            get { return _text; }
        }
    }

    public delegate void FeedbackHandler(object sender, FeedbackEventArgs e);

    public sealed class SqlMethodsGenerator
    {
        public event FeedbackHandler GeneratingProcedure;
        public event FeedbackHandler GeneratedProcedure;

        private readonly CodeTypeDeclaration _codeType;
        private bool _useNullableTypes = false;
        private SymbolNamingHandler _parameterNamer;
        private SymbolNamingHandler _procedureNamer;

        public SqlMethodsGenerator(CodeTypeDeclaration targetCodeType)
        {
            if (targetCodeType == null)
                throw new ArgumentNullException("targetCodeType");

            _codeType = targetCodeType;
            _parameterNamer = MapSymbolName;
            _procedureNamer = MapSymbolName;
        }

        public CodeTypeDeclaration TargetCodeType
        {
            get { return _codeType; }
        }

        public SymbolNamingHandler ProcedureNamer
        {
            get { return _procedureNamer; }
            set { _procedureNamer = value; }
        }

        public SymbolNamingHandler ParameterNamer
        {
            get { return _parameterNamer; }
            set { _parameterNamer = value; }
        }

        public bool UseNullableTypes
        {
            get { return _useNullableTypes; }
            set { _useNullableTypes = value; }
        }

        public void AddUserProcedures(SqlConnection connection)
        {
            //
            // Validate arguments
            //

            if (connection == null)
                throw new ArgumentNullException("connection");

            if (connection.State != ConnectionState.Open)
                throw new InvalidOperationException("The connection must be in an open state prior to calling this function.");

            //
            // Get all the user-stored procedures in the database of the connection
            // and add them.
            //

            DataTable table = connection.GetSchema("Procedures");

            List<string> names = new List<string>(table.Rows.Count);

            DataColumn nameColumn = table.Columns["ROUTINE_NAME"];
            DataColumn typeColumn = table.Columns["ROUTINE_TYPE"];

            foreach (DataRow row in table.Rows)
            {
                string name = row[nameColumn].ToString();

                if (row[typeColumn].ToString().Equals("PROCEDURE", StringComparison.OrdinalIgnoreCase) 
                    && !name.StartsWith("sp_", StringComparison.OrdinalIgnoreCase))
                {
                    names.Add(name);
                }
            }

            AddProcedures(connection, names.ToArray());
        }

        public void AddProcedures(SqlConnection connection, string[] procedureNames)
        {
            //
            // Validate arguments
            //

            if (connection == null)
                throw new ArgumentNullException("connection");

            if (procedureNames == null)
                throw new ArgumentNullException("procedureNames");

            if (connection.State != ConnectionState.Open)
                throw new InvalidOperationException("The connection must be in an open state prior to calling this function.");

            //
            // Use SqlCommandBuilder.DeriveParameters to get the metadata of each
            // stored procedure and generate corresponding methods in the type.
            //

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;

            SqlCommandMethodPairGenerator methodsGenerator = new SqlCommandMethodPairGenerator(this);

            foreach (string procedureName in procedureNames)
            {
                command.CommandText = procedureName;
                command.Parameters.Clear();

                try
                {
                    SqlCommandBuilder.DeriveParameters(command);
                }
                catch (InvalidOperationException e)
                {
                    throw new CodeGeneratorException("The metadata for the '" + procedureName + "' stored procedure could not be retrieved from SQL Server. The stored procedure may not exist, is a system or extended stored procedure, or the supplied connection does not have sufficient privileges.", e);
                }

                //
                // Create an In/Out method for the stored procedure. The "in"
                // method has all the in and in/out parameters whereas the "out" 
                // method has in/out and out parameters.
                //

                methodsGenerator.SqlCommand = command;
                methodsGenerator.GenerateMethods();
            }
        }

        public static string MapSymbolName(string name)
        {
            //
            // Validate arguments
            //

            if (name == null)
                throw new ArgumentNullException("name");

            //
            // Replace all non-alphanumeric and underscore characters with
            // an underscore. If name starts with a digit, then also replace
            // it with an underscore since most languages won't allow symbols
            // starting with a digit. However, chances are that starting
            // with underscore is allowed.
            //

            return Regex.Replace(name, @"(^[0-9])|[^0-9A-Za-z_]", "_");
        }

        private sealed class SqlCommandMethodPairGenerator
        {
            internal readonly SqlMethodsGenerator Parent;
            private SqlCommand _command;
            
            internal SqlCommandMethodPairGenerator(SqlMethodsGenerator parent)
            {
                Parent = parent;
            }

            internal SqlCommand SqlCommand
            {
                get { return _command; }
                set { _command = value; }
            }

            public void GenerateMethods()
            {
                Debug.Assert(_command != null);

                FeedbackEventArgs eventArgs = new FeedbackEventArgs(_command.CommandText);

                if (Parent.GeneratingProcedure != null)
                    Parent.GeneratingProcedure(Parent, eventArgs);

                GenerateInMethod();
                GenerateOutMethod();

                if (Parent.GeneratedProcedure != null)
                    Parent.GeneratedProcedure(Parent, eventArgs);
            }

            private string MethodName
            {
                get
                {
                    return Parent.ProcedureNamer != null ?
                        Parent.ProcedureNamer(_command.CommandText) : _command.CommandText;
                }
            }

            private void GenerateInMethod()
            {
                CodeMemberMethod codeMethod = new CodeMemberMethod();
            
                codeMethod.Name = MethodName;
                codeMethod.Attributes = MemberAttributes.Public | MemberAttributes.Abstract;
                codeMethod.ReturnType = new CodeTypeReference(typeof(SqlCommand));
                codeMethod.CustomAttributes.Add(new CodeAttributeDeclaration("SqlMethodAttribute"));

                if (string.Compare(codeMethod.Name, _command.CommandText, true) != 0)
                    codeMethod.CustomAttributes[0].Arguments.Add(new CodeAttributeArgument("CommandText", new CodePrimitiveExpression(_command.CommandText)));

                codeMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(SqlConnection), "connection"));

                SqlParameter[] commandInParameters = GetParametersWithDirection(ParameterDirection.Input);
                GenerateParameters(commandInParameters, codeMethod, FieldDirection.In);

                Parent.TargetCodeType.Members.Add(codeMethod);
            }

            private void GenerateOutMethod()
            {
                SqlParameter[] commandOutParameters = GetParametersWithDirection(ParameterDirection.Output);
            
                if (commandOutParameters.Length == 0)
                    return;

                CodeMemberMethod codeMethod = new CodeMemberMethod();
                codeMethod.Name = "End" + MethodName;
                codeMethod.Attributes = MemberAttributes.Public | MemberAttributes.Abstract;
                codeMethod.CustomAttributes.Add(new CodeAttributeDeclaration("SqlOutMethodAttribute"));

                codeMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(SqlCommand), "command"));
                GenerateParameters(commandOutParameters, codeMethod, FieldDirection.Out);

                Parent.TargetCodeType.Members.Add(codeMethod);
            }

            private void GenerateParameters(SqlParameter[] commandParameters, 
                CodeMemberMethod codeMethod, FieldDirection inOutDirection)
            {
                Debug.Assert(commandParameters != null);
                Debug.Assert(codeMethod != null);

                foreach (SqlParameter sqlParameter in commandParameters)
                {
                    Debug.Assert(sqlParameter.ParameterName.StartsWith("@"));

                    string inferredName = sqlParameter.ParameterName.Substring(1);              
                    
                    string paramName = inferredName;
                
                    if (Parent.ParameterNamer != null)
                        paramName = Parent.ParameterNamer(paramName);

                    Type paramType = InferType(sqlParameter);
                
                    if (paramType == null)
                    {
                        throw new CodeGeneratorException(string.Format("The data type of '{0}' in '{1}' is not supported.",
                            sqlParameter.ParameterName, _command.CommandText));
                    }

                    CodeParameterDeclarationExpression codeParameter = new CodeParameterDeclarationExpression(paramType, paramName);

                    if (sqlParameter.Direction == ParameterDirection.InputOutput)
                        codeParameter.Direction = inOutDirection;
                    else if (sqlParameter.Direction == ParameterDirection.Output)
                        codeParameter.Direction = FieldDirection.Out;

                    ArrayList codeAttributeArguments = new ArrayList();

                    codeAttributeArguments.Add(new CodeAttributeArgument(new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(SqlDbType)), Enum.GetName(typeof(SqlDbType), sqlParameter.SqlDbType))));

                    if (string.Compare(inferredName, paramName, true) != 0)
                        codeAttributeArguments.Add(new CodeAttributeArgument("Name", new CodePrimitiveExpression(sqlParameter.ParameterName)));

                    if (sqlParameter.Size != 0)
                        codeAttributeArguments.Add(new CodeAttributeArgument("Size", new CodePrimitiveExpression(sqlParameter.Size)));

                    if (sqlParameter.Precision != 0)
                        codeAttributeArguments.Add(new CodeAttributeArgument("Precision", new CodePrimitiveExpression(sqlParameter.Precision)));

                    if (sqlParameter.Scale != 0)
                        codeAttributeArguments.Add(new CodeAttributeArgument("Scale", new CodePrimitiveExpression(sqlParameter.Scale)));

                    if (codeAttributeArguments.Count != 0)
                        codeParameter.CustomAttributes.Add(new CodeAttributeDeclaration("SqlParameterAttribute", (CodeAttributeArgument[]) codeAttributeArguments.ToArray(typeof(CodeAttributeArgument))));

                    codeMethod.Parameters.Add(codeParameter);
                }
            }

            private Type InferType(SqlParameter parameter)
            {
                Debug.Assert(parameter != null);

                if (Parent.UseNullableTypes)
                {
                    switch (parameter.SqlDbType)
                    {
                        case SqlDbType.BigInt : return typeof(SqlInt64);
                        case SqlDbType.Binary : return typeof(SqlBinary);
                        case SqlDbType.Bit : return typeof(SqlBoolean);
                        case SqlDbType.Char : return typeof(SqlString);
                        case SqlDbType.DateTime : return typeof(SqlDateTime);
                        case SqlDbType.Decimal : return typeof(SqlDecimal);
                        case SqlDbType.Float : return typeof(SqlDouble);
                        case SqlDbType.Image : return typeof(SqlBinary);
                        case SqlDbType.Int : return typeof(SqlInt32);
                        case SqlDbType.Money : return typeof(SqlMoney);
                        case SqlDbType.NChar : return typeof(SqlString);
                        case SqlDbType.NText : return typeof(SqlString);
                        case SqlDbType.NVarChar : return typeof(SqlString);
                        case SqlDbType.Real : return typeof(SqlSingle);
                        case SqlDbType.SmallDateTime : return typeof(SqlDateTime);
                        case SqlDbType.SmallInt : return typeof(SqlInt16);
                        case SqlDbType.SmallMoney : return typeof(SqlMoney);
                        case SqlDbType.Text : return typeof(SqlString);
                        case SqlDbType.Timestamp : return typeof(SqlBinary);
                        case SqlDbType.TinyInt : return typeof(SqlByte);
                        case SqlDbType.UniqueIdentifier : return typeof(SqlGuid);
                        case SqlDbType.VarBinary : return typeof(SqlBinary);
                        case SqlDbType.VarChar : return typeof(SqlString);
                        case SqlDbType.Variant : return typeof(Object);
                
                        default : return null;
                    }
                }
                else
                {
                    switch (parameter.SqlDbType)
                    {
                        case SqlDbType.BigInt : return typeof(Int64);
                        case SqlDbType.Binary : return typeof(Byte[]);
                        case SqlDbType.Bit : return typeof(Boolean);
                        case SqlDbType.Char : return typeof(String);
                        case SqlDbType.DateTime : return typeof(DateTime);
                        case SqlDbType.DateTime2 : return typeof(DateTime);
                        case SqlDbType.DateTimeOffset : return typeof(DateTimeOffset);
                        case SqlDbType.Decimal : return typeof(Decimal);
                        case SqlDbType.Float : return typeof(Double);
                        case SqlDbType.Image : return typeof(Byte[]);
                        case SqlDbType.Int : return typeof(Int32);
                        case SqlDbType.Money : return typeof(Decimal);
                        case SqlDbType.NChar : return typeof(String);
                        case SqlDbType.NText : return typeof(String);
                        case SqlDbType.NVarChar : return typeof(String);
                        case SqlDbType.Real : return typeof(Single);
                        case SqlDbType.SmallDateTime : return typeof(DateTime);
                        case SqlDbType.SmallInt : return typeof(Int16);
                        case SqlDbType.SmallMoney : return typeof(Decimal);
                        case SqlDbType.Text : return typeof(String);
                        case SqlDbType.Timestamp : return typeof(DateTime);
                        case SqlDbType.TinyInt : return typeof(Byte);
                        case SqlDbType.UniqueIdentifier : return typeof(Guid);
                        case SqlDbType.VarBinary : return typeof(Byte[]);
                        case SqlDbType.VarChar : return typeof(String);
                        case SqlDbType.Variant : return typeof(Object);
                
                        default : return null;
                    }
                }
            }

            private SqlParameter[] GetParametersWithDirection(ParameterDirection direction)
            {
                ArrayList parameters = new ArrayList(_command.Parameters.Count);

                foreach (SqlParameter parameter in _command.Parameters)
                {
                    if (parameter.Direction == direction ||
                        parameter.Direction == ParameterDirection.InputOutput)
                    {
                        parameters.Add(parameter);
                    }
                }

                return (SqlParameter[]) parameters.ToArray(typeof(SqlParameter));
            }
        }
    }
}
